﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Windows.Forms;
using Control;

namespace LPV_POS
{
    public class thuvien_CL
    {
        public thuvien_CL(string clien, string databas, string usid, string pas)
        {
            //---if (Chuoi_CSDL.IndexOf("@") >= 0)
            if (clien != "")
                this.client = clien;
            if (databas != "")
                this.database = databas;
            if (userid != "")
                this.userid = usid;
            if (pas != "")
                this.pass = pas;
            this.Connect();
        }

        // khai bao cac bien thanh vien
       // public static String Chuoi_CSDL = @"Server=@Client; Database=@Database; uid=@uid; Password=@Password;";
        private SqlConnection mKet_noi;
        string client="@Server";
        string database = "@Database";
        string userid="@uid"; 
        string pass="@Password";
    
        #region"    GetDatatable2"
        public int ExecUpdate2(string _sSQL) //---Client----
        {
            DataTable tb = new DataTable();
            try
            {
                this.Connect();
                //
                SqlCommand cmd = new SqlCommand(_sSQL,mKet_noi);
                int i=cmd.ExecuteNonQuery();
                return i;
            }
            catch (Exception ex) { string s = ex.Message; }
            return -1;
        }

        #region "   Save2SQL"
        public int ExecUpdate2_Config(DataSet _ds)
        {
            string serr = "";
            try
            {
                //clsData oclsData = new clsData(mKet_noi);
                //DAL.clsData tv = new DAL.clsData();
                int iketqua = 0;

                string sSQL = "SELECT a.name " +//load tat ca TRIGGER trong 1 table
                            "FROM dbo.sysobjects  a " +
                            "    LEFT JOIN sysobjects s ON s.id = a.parent_obj " +
                            "WHERE OBJECTPROPERTY(a.id, N'IsTrigger') = 1  " +
                            "    AND s.[name] = '@name' ";

                foreach (DataTable tb1 in _ds.Tables)
                {
                    DataTable tb = new DataTable(tb1.TableName);
                    foreach (DataColumn col1 in tb1.Columns)
                    {
                        DataColumn col = new DataColumn(col1.ColumnName, col1.DataType);
                        tb.Columns.Add(col);
                    }
                    foreach (DataRow dr1 in tb1.Rows)
                    {
                        DataRow dr = tb.NewRow();
                        dr.ItemArray = dr1.ItemArray;
                        tb.Rows.Add(dr);
                    }

                    serr = tb.TableName;
                    DataTable tbKiemTra_SoCot = this.GetDatatable2("SELECT * FROM " + serr + " WHERE 1=2");
                    if (tbKiemTra_SoCot.Columns.Count != tb.Columns.Count)
                    {
                        foreach (DataColumn col in tbKiemTra_SoCot.Columns)
                        {
                            if (!(tb.Columns.Contains(col.ColumnName)))
                            {
                                DataColumn col1 = new DataColumn(col.ColumnName, col.DataType);
                                col1.DefaultValue = col.DefaultValue;
                                tb.Columns.Add(col1);
                            }
                        }

                    }
                    //
                    DataTable tbTrigger = this.GetDatatable2(sSQL.Replace("@name", tb.TableName));
                    string sTrigger = "";
                    foreach (DataRow dr in tbTrigger.Rows)
                        sTrigger += "DISABLE TRIGGER " + dr["name"] + " ON " + tb.TableName + "; ";
                    if (sTrigger != "")
                        this.ExecUpdate2(sTrigger);
                    //
                    //iketqua = this.ExecUpdate2(_ds);
                    iketqua += this.SP_Updatetable(tb.TableName, tb);
                    if (sTrigger != "")
                        this.ExecUpdate2(sTrigger.Replace("DISABLE TRIGGER ", "ENABLE TRIGGER "));

                }
                //if (iketqua < 1)
                //{
                //    MessageBox.Show("Không chuyển được dữ liệu nào");
                //    return false;
                //}

                return iketqua;
            }
            catch (Exception ex)
            {
                string ex1 = ex.Message;
                return -1;
            }
        }
        #endregion

        public DataTable GetDatatable2(string _sSQL)//---Client----
        {
            DataTable tb = new DataTable();
            try
            {
                this.Connect();

                SqlCommand cmd = new SqlCommand(_sSQL,mKet_noi);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(tb);
            }
            catch (Exception ex)
            {
                string s = ex.Message;
            }
            return tb;
        }

        #endregion
        //-----------------------------------------------------
        public int SP_Updatetable(string table_name, DataTable tb)
        {
            this.Connect();

            int irt = 0;
            SqlDataAdapter da = new SqlDataAdapter();
            try
            {
                da.SelectCommand = new SqlCommand("Select * from " + table_name,mKet_noi);
                da.TableMappings.Clear();
                da.TableMappings.Add("Table", table_name);
                SqlCommandBuilder objbuilder = new SqlCommandBuilder(da);

                if (tb.Rows.Count > 0)
                {
                    irt = da.Update(tb);
                    if (irt > 0)
                        tb.GetChanges();
                }
            }
            catch (Exception ex)
            {
                string s = ex.Message;
            }
            return irt;

        }

        //-----------------------------------------------------
        //Thêm, xóa, sửa dữ liệu đến database. Trả về -1 nếu thất bại
        public int SP_Update(String sp_name, String[] paramse, Object[] values)
        {
            int result = 0;

            SqlCommand command = new SqlCommand(sp_name, mKet_noi);
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = 1200;
            SqlTransaction MyTrans = mKet_noi.BeginTransaction();
            command.Transaction = MyTrans;
            try
            {
                if (paramse != null)
                {
                    int len = paramse.Length - 1;
                    int i;
                    for (i = 0; i <= len; i++)
                    {
                        command.Parameters.Add(new SqlParameter(paramse[i], values[i]));
                    }
                }
                command.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int, 0));
                command.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;

                object irt = command.ExecuteNonQuery();
                MyTrans.Commit();
                result = (int)command.Parameters["@RETURN_VALUE"].Value;
            }
            catch (Exception ex)
            {

                string s = ex.Message;
                return -1; //thất bại
            }
            finally
            {
                command.Dispose();
                MyTrans.Dispose();
                command = null;
                mKet_noi.Close();
            }
            return result;
        }

        //-----------------------------

        #region "   Connect"
        void Connect()
        {
            try
            {
                string Chuoi_CSDL = @"Server=" + client + "; Database=" + database + "; uid=" + userid + "; Password=" + pass + ";";
                mKet_noi = new SqlConnection(Chuoi_CSDL);
                if (mKet_noi.State == ConnectionState.Open)
                    mKet_noi.Close();
                mKet_noi.Open();
            }
            catch (Exception ex) { string s = ex.Message; }

        }
        #endregion

    }
}



